package net.tngou.db.query;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;

import org.apache.lucene.search.SortField;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.update.Update;
import net.tngou.db.entity.Field;
import net.tngou.db.entity.Limit;
import net.tngou.db.entity.Page;
import net.tngou.db.entity.Request;
import net.tngou.db.entity.Field.Type;
import net.tngou.db.i18n.I18N;
import net.tngou.db.lucene.LuceneManage;
import net.tngou.db.manage.SQL;
import net.tngou.db.netty.RestartServer;
import net.tngou.db.util.ResultSet;



public class Query {
	
	private static Logger log = LoggerFactory.getLogger(Query.class);
	
	public ResultSet sql(String sql) {
		

		Request request;
		ResultSet rt = new ResultSet();
		try {
			
			request = _parser(sql);
			SQL sqlClass = _retrieveModule(request.getModule());
			 rt = sqlClass.execute(request);
			 log.info(I18N.getValue("sql",sql));
		} catch (JSQLParserException e) {
			e.printStackTrace();
			log.error(I18N.getValue("sql_error",sql));
			rt.setStatus(ResultSet.T_ERROR_SQL);  //SQL 语句不合法
//			rt.setMsg(sql);
		}
	
		return 	rt;
		
		
	}

	private Request _parser(String sql) throws JSQLParserException {
		
		Request request = new Request();
		request.setSql(sql);//
		CCJSqlParserManager manager = new CCJSqlParserManager();
		
		Statement statement = manager.parse(new StringReader(sql));
		
		
		if(statement instanceof CreateTable)
		{
			CreateTable createTable =(CreateTable) statement;
			request.setModule("DDL");
			request.setAction("create");
			String table = createTable.getTable().getName();
			request.setParameter("table", table);
			
		}
		else if(statement instanceof Drop)
		{
			Drop drop =(Drop) statement;
			request.setModule("DDL");
			request.setAction("drop");
			String table = drop.getName();
			request.setParameter("table", table);
			
		}
		else if(statement instanceof Insert)
		{
			
			Insert insert =(Insert) statement;
			request.setModule("DML");
			request.setAction("insert");
			request.setParameter("table", insert.getTable().getName());	
	        List<Field> list = new ArrayList<Field>();
			List<Column> fields = insert.getColumns();
			List<Expression> values = ((ExpressionList) insert.getItemsList()).getExpressions();
			
			for (int i = 0; i < fields.size(); i++) {
				String[] names = fields.get(i).getColumnName().split(":");
				Type type= Type.String;
				if(names.length>1)
				{
					switch (names[1].toLowerCase()) {
					case "key":
						 type= Type.Key;
						break;
					case "string":
						 type= Type.String;
						break;
					case "text":
						 type= Type.Text;
						break;
					default:
						type= Type.String;
						break;
					}
				}
				String value="";
				if(i<values.size())		
				value= ((StringValue)values.get(i)).getValue();
				Field field = new Field(names[0], value, type);
				list.add(field);
				
			}
			
			request.setParameter("fields", list);
			
		}
		else if(statement instanceof Delete)
		{
			Delete delete =(Delete) statement;
			request.setModule("DML");
			request.setAction("delete");
			request.setParameter("table", delete.getTable().getName());
            Expression where = delete.getWhere();
			Field field=new Field(
					((EqualsTo)where).getLeftExpression().toString(), 
					((StringValue)((EqualsTo)where).getRightExpression()).getValue(), 
					Type.Key);
			request.setParameter("field", field);
		}
		
		else if(statement instanceof Update)
		{
			Update update = (Update) statement;
			request.setModule("DML");
			request.setAction("update");
			request.setParameter("table", update.getTables().get(0).getName());
			
            List<Field> list = new ArrayList<Field>();
			List<Column> fields = update.getColumns();
			List<Expression> values = update.getExpressions();
			
			for (int i = 0; i < fields.size(); i++) {
				
				String[] names = fields.get(i).getColumnName().split(":");
				Type type= Type.String;
				if(names.length>1)
				{
					switch (names[1].toLowerCase()) {
					case "key":
						 type= Type.Key;
						break;
					case "string":
						 type= Type.String;
						break;
					case "text":
						 type= Type.Text;
						break;
					default:
						type= Type.String;
						break;
					}
				}
				String value="";
				if(i<values.size())		
				value= ((StringValue)values.get(i)).getValue();
				Field field = new Field(names[0], value, type);
				list.add(field);
			}
		
			request.setParameter("fields", list);
			Expression where = update.getWhere();	
			Field term=new Field(
					((EqualsTo)where).getLeftExpression().toString(), 
					((StringValue)((EqualsTo)where).getRightExpression()).getValue(), 
					Type.Key);
			request.setParameter("term", term);
			
		}else if(statement instanceof Select)
		{
			Select select = (Select) statement;
			request.setModule("DQL");
			request.setAction("select");
			
			PlainSelect s = (PlainSelect) select.getSelectBody();
			List<String> tables= new ArrayList<String>();
			tables.add(((Table)s.getFromItem()).getName()); //表
			List<Join> jts = s.getJoins();  //级联的表
			if(jts!=null)
			{
			for (Join join : jts) {
				tables.add(((Table)join.getRightItem()).getName());
				}
			}
					
			request.setParameter("tables", tables);  //数据库表
			
			List<String> items= new ArrayList<String>();
			List<SelectItem> sitems = s.getSelectItems();
			
			sitems.forEach(e->{
				items.add(e.toString());
			});
			
			request.setParameter("items", items);  //数据字段
			
			
			Limit limit = new Limit();	  //limit 取数据
			net.sf.jsqlparser.statement.select.Limit lit = s.getLimit();	
			if(lit!=null)
			{
				limit.setStart((int) lit.getOffset());
				limit.setSize((int) lit.getRowCount());	
			}
			
			request.setParameter("limit", limit);
			
			
			List<SortField> sortFields = new ArrayList<SortField>(); //order by 排序
			List<OrderByElement> orders = s.getOrderByElements();
			if(orders!=null)
			{
				orders.forEach(e->{
					OrderByElement order=e;
				
					org.apache.lucene.search.SortField.Type type = org.apache.lucene.search.SortField.Type.LONG;
					
					
					String[] names = order.getExpression().toString().split(":");
				
					if(names.length>1)
					{
						switch (names[1].toLowerCase()) {
						case "key":
							 type= org.apache.lucene.search.SortField.Type.LONG;
							break;
						case "string":
							 type= org.apache.lucene.search.SortField.Type.STRING;
							break;		
						default:
							type= org.apache.lucene.search.SortField.Type.LONG;
							break;
						}
					}
					
					
					boolean reverser = true; //倒叙
					if(order.isAsc() )reverser =false;
					SortField sortField = new SortField(names[0],type, reverser);
					sortFields.add(sortField);
				});
			}
			
			request.setParameter("sortFields", sortFields);
			
			
			List<Field> wheres = new ArrayList<Field>();
			Expression where = s.getWhere();
			if(where!=null)
			{
				if(where instanceof EqualsTo)
				{
					EqualsTo equalsTo = (EqualsTo) where;
					 wheres.add(_getField(equalsTo));
				}
				else if(where instanceof AndExpression)
				{
				 
					AndExpression expression =(AndExpression) where;
				
					
					Expression left =null;
					Expression right=null;
					do {
					
						left = expression.getLeftExpression();
						right = expression.getRightExpression();
						if(right instanceof EqualsTo)
						{
							EqualsTo equalsTo = (EqualsTo) right;
							wheres.add(_getField(equalsTo));
						}
						if(left instanceof EqualsTo)
						{
							EqualsTo equalsTo = (EqualsTo) left;
							wheres.add(_getField(equalsTo));
						}else
						{
							expression=(AndExpression) left;
						}

					} while (left instanceof AndExpression);
					
				}
				
			}
			
			request.setParameter("wheres", wheres);
		}

		return request ;
	}
	
	
	  private  SQL _retrieveModule(String module) 
	  {
		 
		 
		 
		 String packages="net.tngou.db.manage";
		 String moduleClass=packages+"."+module;
			try {
					
				SQL baseAction = (SQL) Class.forName(moduleClass).newInstance();
				 return baseAction;
				} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
					e.printStackTrace();
				} 
				
			
			return null;
		
	  }

	  
		private  Field _getField(EqualsTo equalsTo) {

			String[] names = equalsTo.getLeftExpression().toString().split(":");
			Type type= Type.String;
			if(names.length>1)
			{
				switch (names[1].toLowerCase()) {
				case "key":
					 type= Type.Key;
					break;
				case "string":
					 type= Type.String;
					break;
				case "text":
					 type= Type.Text;
					break;
				default:
					type= Type.String;
					break;
				}
			}
			String value=((StringValue)equalsTo.getRightExpression()).getValue();
			Field field = new Field(names[0], value, type);
			return field;
		}
	  
	 
}
